package model

import (
	"fmt"
	"tools-api/src/common/db/database"
	"tools-api/src/common/utils"
)

// StatisticsBill 账单
type StatisticsBill struct {
}

// StatisticsBillDay 每日收支账单
type StatisticsBillDay struct {
	Date   string
	Income float64
	Payout float64
}

// StatisticsBillTypes 按类别分组
type StatisticsBillTypes struct {
	Type      int     `json:"type"`
	TypesId   int64   `json:"types_id"`
	TypesName string  `json:"types_name"`
	TypesKey  string  `json:"types_key"`
	Count     int     `json:"count"`
	Money     float64 `json:"money"`
}

// GroupByMonth 月账单
func (StatisticsBill) GroupByMonth(uuid string, bookId, accountId int64, year, dayStart int) []map[string]interface{} {
	dayStartStr := utils.AddZero(dayStart)
	yearStart := fmt.Sprintf("%d-01-%s", year, dayStartStr)
	var yearEnd string
	if dayStart == 1 {
		yearEnd = fmt.Sprintf("%d-12-%d", year, 31)
	} else { // 非1号账单日，那么截止日期需要下一年
		yearEnd = fmt.Sprintf("%d-01-%s", year+1, utils.AddZero(dayStart-1))
	}
	condition := fmt.Sprintf(" WHERE t.uuid='%s' AND t.book_id=%d", uuid, bookId)
	if accountId >= 0 { // 是否按照收支账户统计，其中0 为不计入账户
		condition += fmt.Sprintf(" AND t.accounts_id=%d", accountId)
	}
	condition += fmt.Sprintf(" AND t.delete_mark=0 AND t.DATE BETWEEN '%s' AND '%s'", yearStart, yearEnd)
	sql := "SELECT"
	sql += fmt.Sprintf(" '%d-01-%s;%s;%s' AS title", year, dayStartStr, yearStart, yearEnd) // "2025-01-01;2025-01-01;2025-12-31"
	sql += ", IFNULL(SUM(CASE WHEN t.type=2 THEN t.money ELSE 0 END), 0) AS income"
	sql += ", IFNULL(SUM(CASE WHEN t.type=1 THEN t.money ELSE 0 END), 0) AS payout"
	sql += ", 1 as type" // 类型 1: 合计
	sql += " FROM book_detail t"
	sql += condition
	sql += " UNION ALL SELECT"

	caseStr := " CASE" // 月份
	for i := 1; i <= 12; i++ {
		monthStr := utils.AddZero(i)
		yearMonth := fmt.Sprintf("%d-%s", year, monthStr)
		dateStart, dateEnd, _ := utils.CalculateStartDateRange(yearMonth, dayStart)
		s := dateStart.Format("2006-01-02")
		e := dateEnd.Format("2006-01-02")
		valueStr := fmt.Sprintf("%s-%s;%s;%s", yearMonth, dayStartStr, s, e) // "2025-08-01;2025-08-01;2025-08-31"
		if i < 12 {
			caseStr += fmt.Sprintf(" WHEN t.DATE BETWEEN '%s' AND '%s' THEN '%s'", s, e, valueStr)
		} else {
			caseStr += fmt.Sprintf(" ELSE '%s' END", valueStr)
		}
	}
	caseStr += " AS title"

	sql += caseStr
	sql += ", SUM(CASE WHEN t.type=2 THEN t.money ELSE 0 END) AS income"
	sql += ", SUM(CASE WHEN t.type=1 THEN t.money ELSE 0 END) AS payout"
	sql += ", 0 as type" // 类型 0: 明细
	sql += " FROM book_detail t"
	sql += condition
	sql += " GROUP BY title"
	sql += " ORDER BY type DESC, title DESC"
	result, _ := database.JCJEngine.QueryInterface(sql)
	return result
}

// GroupByYear 年账单
func (StatisticsBill) GroupByYear(uuid string, bookId, accountId int64, dayStart int) []map[string]interface{} {
	condition := fmt.Sprintf(" WHERE t.uuid='%s' AND t.book_id=%d", uuid, bookId)
	if accountId >= 0 { // 是否按照收支账户统计，其中0 为不计入账户
		condition += fmt.Sprintf(" AND t.accounts_id=%d", accountId)
	}
	condition += " AND t.delete_mark=0"
	sql := "SELECT"
	sql += " '' AS title"
	sql += ", IFNULL(SUM(CASE WHEN t.type=2 THEN t.money ELSE 0 END), 0) AS income"
	sql += ", IFNULL(SUM(CASE WHEN t.type=1 THEN t.money ELSE 0 END), 0) AS payout"
	sql += ", 1 as type" // 类型 1: 合计
	sql += " FROM book_detail t"
	sql += condition
	sql += " UNION ALL SELECT"

	if dayStart == 1 { // 起始日1号
		sql += " CONCAT(YEAR(t.date), '-01-01;', YEAR(t.date), '-01-01;', YEAR(t.date), '-12-31') AS title" // "2025-01-01;2025-01-01;2025-12-31"
	} else {
		dayStartStr := utils.AddZero(dayStart) // 日起始补0
		dayEndStr := utils.AddZero(dayStart - 1)
		// 大于1月起始日 当年 "2025-01-12;2025-01-12;2026-01-11"
		titleStr1 := fmt.Sprintf("CONCAT(YEAR(t.date), '-01-%s;', YEAR(t.date), '-01-%s;', YEAR(t.date)+1, '-01-%s')", dayStartStr, dayStartStr, dayEndStr)
		// 小于1月起始日 上一年 "2024-01-12;2024-01-12;2025-01-11"
		titleStr2 := fmt.Sprintf("CONCAT(YEAR(t.date)-1, '-01-%s;', YEAR(t.date)-1, '-01-%s;', YEAR(t.date), '-01-%s')", dayStartStr, dayStartStr, dayEndStr)

		sql += fmt.Sprintf(" CASE WHEN t.DATE >= CONCAT(YEAR(t.date), '-01-%s') THEN %s", dayStartStr, titleStr1)
		sql += fmt.Sprintf(" ELSE %s", titleStr2)
		sql += " END AS title"
	}

	sql += ", SUM(CASE WHEN t.type=2 THEN t.money ELSE 0 END) AS income"
	sql += ", SUM(CASE WHEN t.type=1 THEN t.money ELSE 0 END) AS payout"
	sql += ", 0 as type" // 类型 0: 明细
	sql += " FROM book_detail t"
	sql += condition
	sql += " GROUP BY title"
	sql += " ORDER BY type DESC, title DESC"
	result, _ := database.JCJEngine.QueryInterface(sql)
	return result
}

// GetDailyList 区间内每日收支
func (StatisticsBill) GetDailyList(uuid string, bookId int64, start, end string) []StatisticsBillDay {
	list := make([]StatisticsBillDay, 0)
	_ = database.JCJEngine.Table(BookDetail{}).Alias("t").
		Select("t.date,SUM(case when t.type=1 then t.money ELSE 0 END) AS payout, SUM(case when t.type=2 then t.money ELSE 0 END) AS income").
		Where("t.uuid=? AND t.book_id=? AND t.delete_mark=0 AND t.date BETWEEN ? AND ?", uuid, bookId, start, end).
		GroupBy("t.date").
		Find(&list)
	return list
}

// GetMonthGroupByTypes 月账单 类别排行榜前10
func (StatisticsBill) GetMonthGroupByTypes(uuid string, bookId int64, start, end string, _type int) []StatisticsBillTypes {
	list := make([]StatisticsBillTypes, 0)
	_ = database.JCJEngine.Table(BookDetail{}).Alias("t").
		Join("LEFT", []string{"book_types", "b"}, "b.id = t.types_id").
		Select("t.type, t.types_id, b.name as types_name, b.key as types_key, SUM(t.money) AS money, count(money) as count").
		Where("t.uuid=? AND t.book_id=? AND t.delete_mark=0 AND t.date BETWEEN ? AND ? AND t.type =?", uuid, bookId, start, end, _type).
		GroupBy("t.type, t.types_id, types_name, types_key").
		Asc("money").
		Limit(10, 0).
		Find(&list)
	return list
}

// GetMonthRank10 月账单 明细排行榜前10
func (StatisticsBill) GetMonthRank10(uuid string, bookId int64, start, end string, _type int) []BookDetailResult {
	list := make([]BookDetailResult, 0)
	defAccount := DefBookAccount
	database.JCJEngine.ShowSQL(true)
	session := database.JCJEngine.Table("book_detail").Alias("t").
		Select("t.id, t.book_id, t.type, t.money, t.date, t.remark, t.types_id, b.key as types_key, b.name as types_name,"+
			" t.accounts_id as account_id, case when a.name is null then '"+defAccount.Name+"' else a.name end as account,"+
			" case when a.key is null then '"+defAccount.Key+"' else a.key end as account_icon").
		Join("LEFT", []string{"book_types", "b"}, "b.id = t.types_id").
		Join("LEFT", []string{"book_accounts", "a"}, "a.id = t.accounts_id").
		Where("t.uuid=? and t.book_id=? and t.delete_mark=0 and t.date between ? and ? AND t.type=?", uuid, bookId, start, end, _type)
	if _type == 1 {
		session = session.Asc("t.money")
	} else {
		session = session.Desc("t.money")
	}
	_ = session.Limit(10, 0).Find(&list)
	database.JCJEngine.ShowSQL(false)
	return list
}
